#!/bin/bash
# File Name: monitor_cmdb_user.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-04-12 18:21:12
# Function: 
#########################################################################
#!/bin/bash
db_name="db_cmdb"
host_list="192.168.2.142"
mysql_port=3306
log_dir="log"
mysql_admin_user="moshan"
mysql_admin_passwd="xxxxxx"
mysql_user="dbms_monitor_r"
mysql_passwd="xxx"
#monitor_user_name="mfw"
monitor_user_name="dbms_search_r"
ha_list_file="${log_dir}/ha.log"
send_log="${log_dir}/send.log"
monitor_log="${log_dir}/monitor.log"
mysql_comm="mysql -u${mysql_admin_user} -p${mysql_admin_passwd} -h${host_list} -P${mysql_port} ${db_name}"
[ ! -d "${log_dir}" ] && mkdir ${log_dir}
stop_file="stop_file"

function f_send_monitor()
{
    error_node=(${1})
    send_file="${2}"
    state="${3}"
    node_role="${4}"
    show_user_name="${5}"
    show_t_time="${6}"
sql_info="${7}"
    [ "${state}x" == "OKx" ] && { show_state="已恢复" ;sts=2;} || { show_state="故障中";sts=0;}
    t_time="$(date "+%F %H:%M:%S")"
cat <<EOF > ${send_file}
告警通知
告警状态:${show_state}
故障节点:${error_node[0]}:${error_node[1]}
节点角色:${node_role}
用户名称:${show_user_name}
执行时间:${show_t_time}(s)
告警时间:${t_time}
告警级别:严重告警
产品线名:${product_info}
项目名称:${project_info}
高可用组:${ha_name}
执行语句:${sql_info}

EOF
    cat <<EOF|${mysql_comm} 2>/dev/null
insert into db_monitor.t_monitor_alert_sendlist(type,title,content,usergroup_id,sts,create_time,send_time) select 'mysql','DB Monitor:查询超时(${error_node[0]}:${error_node[1]})-${show_state}','$(cat ${send_file})',1,${sts},'${t_time}','${t_time}'
EOF
    python3 send.py ${send_file} "DB Monitor:查询超时(${error_node[0]}:${error_node[1]})-${show_state}" >/dev/null 
    if [ $? -eq 0 ]
    then
       send_state="OK"
    else
       send_state="ERROR"
    fi
    echo "insert into t_monitor_alert_sendlist(type,title,content,usergroup_id,sts,create_time,send_time) select 'mysql','DB Monitor:查询超时(${error_node[0]}:${error_node[1]})','$(cat ${send_file})',1,0,'${t_time}','${t_time}'" >> ${monitor_log}
    if [ "${state}x" == "OKx" ]
    then
        echo -e "\033[32m[${t_time}] [查询超时 STATUS ${state}] [SEND STATUS ${send_state}] ${error_node[0]}:${error_node[1]} ${product_info} ${project_info} ${ha_name} \033[0m" >> ${send_log}
        rm -f ${send_file}
    else
        echo -e "\033[31m[${t_time}] [查询超时 STATUS ${state}] [SEND STATUS ${send_state}] ${error_node[0]}:${error_node[1]} ${product_info} ${project_info} ${ha_name} \033[0m" >> ${send_log}
    fi
}

function f_monitor()
{
        ha_name="${1}"
        #ha_name="mgr_hotel_static_data_new"
        product_info="$(grep "^${ha_name}:" ${ha_list_file}|grep ":写节点:"|awk -F: '{print $5}'|tail -1)"
        project_info="$(grep "^${ha_name}:" ${ha_list_file}|grep ":写节点:"|awk -F: '{print $6}'|tr "\n" ","|sed 's/,$//g')"
        write_node=$(grep "^${ha_name}:" ${ha_list_file}|grep ":写节点:"|awk -F: '{print $3}'|tail -1)
        node_port=$(grep "^${ha_name}:" ${ha_list_file}|awk -F: '{print $4}'|tail -1)
        node_array=($(grep "^${ha_name}:" ${ha_list_file}|grep -v ":写节点:"|awk -F: '{print $3}'|tr " " "\n"|sort|uniq|tr "\n" " ") ${write_node})
        for ((i=0;i<${#node_array[@]};i++))
        do
        {
            node_role="$(grep ${node_array[${i}]} ${ha_list_file}|grep "^${ha_name}:"|grep ${node_port}|awk -F: '{print $2}'|tail -1)"
            node_mysql_comm="mysql -u${mysql_user} -p${mysql_passwd} -h${node_array[${i}]} -P${node_port}"
            show_info=$(${node_mysql_comm} -e "show full processlist" 2>/dev/null|grep "${monitor_user_name}"|sort -t $'\t' -k6n|awk -F'\t' '{print $6":::"$8}'|tail -1)
            t_time="$(awk -F::: '{print $1}' <<< "${show_info}"|sed 's/[^0-9]//g')"
            sql_info="$(awk -F::: '{print $2}' <<< "${show_info}"|sed "s/'/\\\'/g")"
            node_send_file="send/${node_array[${i}]}_${node_port}.send"
            if [ "${t_time}x" != "x" ]
            then
                if [ ${t_time} -gt 300 ] 
                then
                    f_send_monitor "${node_array[${i}]} ${node_port}" "${node_send_file}" "ERROR" "${node_role}" "${monitor_user_name}" "${t_time}" "${sql_info}"
                fi
            else
                t_time=0
                if [ -f "${node_send_file}" ]
                then
                    rm -f ${node_send_file} && f_send_monitor "${node_array[${i}]} ${node_port}" "${node_send_file}" "OK" "${node_role}" "${monitor_user_name}" "${t_time}" "${sql_info}"
                fi
            fi
        }
        done
}

while :
do
    [ -f "${stop_file}" ] && exit
    ${mysql_comm} -NBe "select distinct tdhg.ha_group_name, tdhgm.role_name,tsi.ip_app, tdi.port,tpdi.product_name, tpji.project_name from t_server_info tsi join t_db_instance tdi on tsi.server_id = tdi.server_id join t_db_ha_group_member tdhgm on tdhgm.member_id = tdi.instance_id join t_db_ha_group tdhg on tdhg.ha_group_id = tdhgm.ha_group_id join t_db_database tdd on tdd.instance_id = tdi.instance_id join t_project_info tpji on tdd.project_id = tpji.project_id join t_product_info tpdi on tpdi.product_id = tpji.product_id where 1=1 and ha_type='mgr' and tsi.ip_app like '192%' order by tdhg.ha_group_name;" 2>/dev/null |tr "\t" ":" > ${ha_list_file}
    for ha_name in $(grep ":写节点:" ${ha_list_file}|awk -F: '{print $1}'|sort|uniq)
    do
        f_monitor ${ha_name}
    done
    sleep 5
done
